package CP2013Server;

import cp2013.Item;
import cp2013.User;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

import java.text.SimpleDateFormat;
import java.util.*;

public class AuctionDatabase {

    private Connection con;
    private String dbUrl = "jdbc:mysql://localhost:4242/auction_db";
    private String dbClass = "com.mysql.jdbc.Driver";
    private String query;
    private Calendar currentDate = null;
    private SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");

    private void connectDB() {
        try {
            Class.forName(dbClass);
            con = DriverManager.getConnection(dbUrl, "root", "132546MySql");

            if (con == null) {
                System.out.println("no connection");
            }

        } //end try
        catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public User loginQuery(String username, String pass) {
        //query db
        User user = null;
        Statement stmt = null;
        ResultSet rs = null;

        //connect to DB
        if (con == null) {
            this.connectDB();
        }//close if
        
        try {
            query = "SELECT Username, Token, User_ID "
                    + "FROM user "
                    + "WHERE Username = '"
                    + username
                    + "' AND Password = '"
                    + pass
                    +"'";

            stmt = con.createStatement();
            rs = stmt.executeQuery(query);
            if (rs.next()) {
                user = new User(rs.getString("Username"), rs.getInt("Token"), rs.getInt("User_ID"));
            } else {
                user = null;
            }

            stmt.close();
            rs.close();

        } catch (SQLException ex) {
            Logger.getLogger(AuctionDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }

        query = null;
        return user;


    }//end of loginQuery()

    public boolean registerQuery(User user) {
        PreparedStatement st = null;
        try {
            query = "INSERT INTO User ("
                    + "Username, "
                    + "F_name, "
                    + "L_name, "
                    + "Password, "
                    + "Address, "
                    + "Email, "
                    + "DOB, "
                    + "Token, "
                    + "User_Id ) VALUES ("
                    + "?, ?, ?, ?, ?, ?, ?, ? ,?)";
            if (con == null) {
                this.connectDB();
            }
            st = con.prepareStatement(query);
            st.setString(1, user.getUsername());
            st.setString(2, user.getfName());
            st.setString(3, user.getlName());
            st.setString(4, user.getPassword());
            st.setString(5, user.getAddress());
            st.setString(6, user.geteMail());
            st.setString(7, user.getDob());
            st.setString(8, "5");
            st.setString(9, null);
            st.executeUpdate();
            st.close();

        } catch (SQLException ex) {
            Logger.getLogger(AuctionDatabase.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } finally {
            try {
                query = null;
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(AuctionDatabase.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        query = null;
        return true;
    }
    
    public boolean placeItemQuery(Item item) {
        PreparedStatement st = null;
        try {
            query = "INSERT INTO Item ("
                    + "Name, "
                    + "Starting_bid, "
                    + "Seller_name, "
                    + "Start_date, "
                    + "Finish_date, "
                    + "Highest_bidder, "
                    + "Description, "
                    + "Seller_ID) VALUES ("
                    + "?, ?, ?, ?, ?, ?, ?, ?)";
            if (con == null) {
                this.connectDB();
            }
            st = con.prepareStatement(query);
            st.setString(1, item.getItemName());
            st.setString(2, Integer.toString(item.getStartingBid()));
            st.setString(3, item.getSellerName());
            st.setString(4, item.getStartDate());
            st.setString(5, item.getFinishDate());
            st.setString(6, item.getHighestBidder());
            st.setString(7, item.getDescription());
            st.setString(8, Integer.toString(item.getSellerID()));
            st.executeUpdate();
            st.close();

        } catch (SQLException ex) {
            Logger.getLogger(AuctionDatabase.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } finally {
            try {
                query = null;
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(AuctionDatabase.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return true;
    }

    public boolean rechargeQuery(String id, String rechargeAmt) {
        PreparedStatement st = null;
        currentDate = Calendar.getInstance();
        try {
            query = "UPDATE User "
                    + "SET Token = Token + ? "
                    + "WHERE User_Id = ?";

            if (con == null) {
                this.connectDB();
            }
            st = con.prepareStatement(query);
            st.setString(1, rechargeAmt);
            st.setString(2, id);
            st.executeUpdate();

            query = "INSERT INTO Recharge_History ("
                    + "Recharge_amount, "
                    + "Date, "
                    + "Buyer_ID) VALUES ("
                    + "?,?,?)";
            st = con.prepareStatement(query);
            st.setString(1, rechargeAmt);
            st.setString(2, formatter.format(currentDate.getTime()));
            st.setString(3, id);
            st.executeUpdate();


        } catch (SQLException ex) {
            Logger.getLogger(AuctionDatabase.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } finally {
            try {
                query = null;
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(AuctionDatabase.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return true;
    }

    public Queue refreshQuery(){
        Statement stmt = null;
        ResultSet rs = null;
        Queue<Item> itemQ = new LinkedList();
        currentDate = Calendar.getInstance();
        SimpleDateFormat formatterR = new SimpleDateFormat("dd MMMMM yyyy");

        //connect to DB
        if (con == null) {
            this.connectDB();
        }//close if

        String current = formatter.format(currentDate.getTime());
        try {
            query = "SELECT * "
                    + "FROM Item "
                    + "WHERE Finish_date >= \""
                    + current
                    + "\"";

            stmt = con.createStatement();
            rs = stmt.executeQuery(query);

            while (rs.next()) {
                Item i = new Item(rs.getString("Name"), rs.getString("Description"), rs.getString("Seller_ID"), rs.getString("Seller_name"), formatterR.format(rs.getDate("Start_date")), formatterR.format(rs.getDate("Finish_date")), rs.getString("Starting_bid"), rs.getString("Current_bid"), rs.getString("Highest_bidder"));
                i.setItemId(rs.getInt("Item_id"));
                itemQ.add(i);
            }
            stmt.close();
            rs.close();

        } catch (SQLException ex) {
            Logger.getLogger(AuctionDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
        query = null;
        return itemQ;
    }

    public boolean bidQuery(String itemID, String bidValue, String userID) {
        PreparedStatement st = null;
        Statement stmt = null;
        int currentBid = 0;
        currentDate = Calendar.getInstance();
        try {
            query = "SELECT Current_bid "
                    + "FROM item "
                    + "WHERE Item_id = ?";

            st = con.prepareStatement(query);
            st.setString(1, itemID);
            ResultSet rs = st.executeQuery();

            if (rs.next()) {
                currentBid = rs.getInt("Current_bid");
            }
            rs.close();
        } catch (SQLException ex) {
            
        }

        if (Integer.parseInt(bidValue) > currentBid) {

            try {
                query = "INSERT INTO Bid ("
                        + "Item_ID, "
                        + "Bid_price, "
                        + "Date, "
                        + "Buyer_ID) "
                        + "VALUES ("
                        + "?,?,?,?)";

                if (con == null) {
                    this.connectDB();
                }
                st = con.prepareStatement(query);
                st.setString(1, itemID);
                st.setString(2, bidValue);
                st.setString(3, formatter.format(currentDate.getTime()));
                st.setString(4, userID);
                st.executeUpdate();
                st.close();

                query = "UPDATE User "
                        + "INNER JOIN bid ON User.`User_ID` = bid.`Buyer_ID` "
                        + "INNER JOIN item ON bid.`Item_ID` = item.`Item_ID` "
                        + "SET User.`Token` = User.`Token` - "
                        + bidValue
                        + ", item.`Current_Bid` = "
                        + bidValue
                        + ", item.`Highest_bidder` = User.`Username`"
                        + " WHERE User_Id = '"
                        + userID
                        + "' AND BID.`Bid_price` = "
                        + bidValue;

                stmt = con.createStatement();
                stmt.executeUpdate(query);
                stmt.close();

            } catch (SQLException ex) {
                Logger.getLogger(AuctionDatabase.class.getName()).log(Level.SEVERE, null, ex);
                return false;
            } finally {
                try {
                    query = null;
                    st.close();
                } catch (SQLException ex) {
                    Logger.getLogger(AuctionDatabase.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            return true;
        } else {
            return false;
        }

    }

   public Queue viewHistoryQuery(String id) {
       Statement stmt = null;
       ResultSet rs = null;
       Queue<String> viewQ = new LinkedList();
       SimpleDateFormat formatterV = new SimpleDateFormat("dd MMMMM yyyy");

        //connect to DB
        if (con == null) {
            this.connectDB();
        }//close if

        try {
            query = "SELECT Recharge_amount, Date "
                    + "FROM recharge_history "
                    + "WHERE Buyer_ID = '"
                    + id
                    + "'";

            stmt = con.createStatement();
            rs = stmt.executeQuery(query);

            while(rs.next()){
                viewQ.add(rs.getString("Recharge_amount")+";"+formatterV.format(rs.getDate("Date")));
            }
            stmt.close();
            rs.close();

        } catch (SQLException ex) {
            Logger.getLogger(AuctionDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
        query = null;
        return viewQ;
    }

   public Queue viewTransHistory(String id){
       Statement stmt = null;
       ResultSet rs = null;
       Queue<String> viewQ = new LinkedList();
       SimpleDateFormat formatterV = new SimpleDateFormat("dd MMMMM yyyy");

        //connect to DB
        if (con == null) {
            this.connectDB();
        }//close if

        try {
            query = "SELECT Transaction_date, Transaction.`Item_ID`, Item.`Name`, Item.`Current_bid`, item.`Seller_name` "
                    + "FROM TRANSACTION INNER JOIN ITEM "
                    + "ON ITEM.`Item_ID` = TRANSACTION.`ITEM_ID` "
                    + "WHERE transaction.`Buyer_ID` = "
                    + id;

            stmt = con.createStatement();
            rs = stmt.executeQuery(query);

            while(rs.next()){
                //date, item id, name, bid, seller,
                viewQ.add(formatterV.format(rs.getDate("Transaction_date"))+";"+ rs.getString("Item_ID")+";"+rs.getString("Name")+";"+rs.getString("Current_bid")+";"+rs.getString("Seller_name"));
                System.out.println(viewQ.peek());
            }
            stmt.close();
            rs.close();

        } catch (SQLException ex) {
            Logger.getLogger(AuctionDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
        query = null;
        return viewQ;
   }

   public boolean transQuery(){
       PreparedStatement st = null;
       currentDate = Calendar.getInstance();
       try{
//           System.out.println("Trans Query");
           query = "INSERT INTO transaction (Transaction_Date, Item_ID, Buyer_ID, Seller_ID)"
                   + "SELECT ITEM.`Finish_date`, ITEM.`Item_ID`, USER.`USER_ID`, ITEM.`Seller_ID` "
                   + "FROM ITEM INNER JOIN USER "
                   + "ON ITEM.`Highest_bidder` = USER.`USERNAME` "
                   + "WHERE ITEM.`Finish_date` < (?) "
                   + "AND ITEM.`STATUS` = \"ACTIVE\"";

           if (con == null) {
               this.connectDB();
            }

            st = con.prepareStatement(query);
            st.setString(1, formatter.format(currentDate.getTime()));
            st.executeUpdate();

            query = "UPDATE ITEM "
                    + "SET ITEM.`STATUS` = \"INACTIVE\" "
                    + "WHERE ITEM.`Finish_date` < (?) ";

            st = con.prepareStatement(query);
            st.setString(1, formatter.format(currentDate.getTime()));
            st.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(AuctionDatabase.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } finally {
            try {
                query = null;
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(AuctionDatabase.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return true;
    }
   
 public Queue manageQuery(){
        Statement stmt = null;
        ResultSet rs = null;
        Queue<Item> itemQ = new LinkedList();
        currentDate = Calendar.getInstance();
        SimpleDateFormat formatterR = new SimpleDateFormat("dd MMM yy");

        //connect to DB
        if (con == null) {
            this.connectDB();
        }//close if

        try {
            query = "SELECT * "
                    + "FROM Item ";

            stmt = con.createStatement();
            rs = stmt.executeQuery(query);

            while (rs.next()) {
                Item i = new Item(rs.getString("Name"), rs.getString("Description"), rs.getString("Seller_ID"), rs.getString("Seller_name"), formatterR.format(rs.getDate("Start_date")), formatterR.format(rs.getDate("Finish_date")), rs.getString("Starting_bid"), rs.getString("Current_bid"), rs.getString("Highest_bidder"));
                i.setItemId(rs.getInt("Item_id"));
                i.setStatus(rs.getString("Status"));
                itemQ.add(i);
            }
            stmt.close();
            rs.close();

        } catch (SQLException ex) {
            Logger.getLogger(AuctionDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
        query = null;
        return itemQ;
    }
 
    public boolean deleteQuery(int id) {
        PreparedStatement st = null;
        currentDate = Calendar.getInstance();
        try {
            query = "DELETE FROM Item "
                    + "WHERE Item_ID = ?";

            if (con == null) {
                this.connectDB();
            }
            st = con.prepareStatement(query);
            st.setInt(1, id);
            st.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(AuctionDatabase.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } finally {
            try {
                query = null;
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(AuctionDatabase.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return true;
    }
    public boolean updateQuery(String column, int id, String newValue) {
        PreparedStatement st = null;
        currentDate = Calendar.getInstance();
        try {
            query = "UPDATE Item "
                    + "SET "
                    +column
                    + " = ? "
                    + "WHERE Item_ID = ?";

            if (con == null) {
                this.connectDB();
            }
            st = con.prepareStatement(query);
            st.setString(1, newValue);
            st.setInt(2, id);
            st.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(AuctionDatabase.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } finally {
            try {
                query = null;
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(AuctionDatabase.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return true;
    }
}
